Skip Headers

Oracle® Database Application Developer's Guide - Large Objects 10g Release 2 (10.2) Part Number B14249-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 6 Overview of Supplied LOB APIs

This chapter contains these topics:

- Programmatic Environments That Support LOBs

- Comparing the LOB Interfaces

- Using PL/SQL (DBMS_LOB Package) to Work with LOBs

- Using OCI to Work with LOBs

- Using C++ (OCCI) to Work with LOBs

- Using C/C++ (Pro*C) to Work with LOBs

- Using COBOL (Pro*COBOL) to Work with LOBs

- Using Visual Basic (Oracle Objects for OLE) to Work with LOBs

- Using Java (JDBC) to Work with LOBs

- Oracle Provider for OLE DB (OraOLEDB)

- Overview of Oracle Data Provider for .NET (ODP.NET)

## Programmatic Environments That Support LOBs

Table 6-1 lists the programmatic environments that support LOB functionality.

See Also: APIs for supported LOB operations are described in detail in: Chapter 12, "Operations Specific to Persistent and Temporary LOBs" Chapter 14, " LOB APIs for Basic Operations" Chapter 15, "LOB APIs for BFILE Operations"

Table 6-1 Programmatic Environments That Support LOBs

Language | Precompiler or Interface Program | Syntax Reference | In This Chapter See...
--- | --- | --- | ---
PL/SQL | DBMS_LOB Package | Oracle Database PL/SQL Packages and Types Reference | "Using PL/SQL (DBMS_LOB Package) to Work with LOBs".
C | Oracle Call Interface for C (OCI) | Oracle Call Interface Programmer's Guide | "Using OCI to Work with LOBs".
C++ | Oracle Call Interface for C++ (OCCI) | Oracle C++ Call Interface Programmer's Guide | "Using C++ (OCCI) to Work with LOBs" .
C/C++ | Pro*C/C++ Precompiler | Pro*C/C++ Programmer's Guide | "Using C/C++ (Pro*C) to Work with LOBs".
COBOL | Pro*COBOL Precompiler | Pro*COBOL Programmer's Guide | "Using COBOL (Pro*COBOL) to Work with LOBs".
Visual Basic | Oracle Objects For OLE (OO4O) | Oracle Objects for OLE (OO4O) is a Windows-based product included with the database. There are no manuals for this product, only online help. Online help is available through the Application Development submenu of the database installation. | "Using Visual Basic (Oracle Objects for OLE) to Work with LOBs"."
Java | JDBC Application Programmatic Interface (API) | Oracle Database JDBC Developer's Guide and Reference. | "Using Java (JDBC) to Work with LOBs".
OLEDB | OraOLEDB, an OLE DB provider for Oracle. | Oracle Provider for OLE DB Developer's Guide | "Oracle Provider for OLE DB (OraOLEDB)"

## Comparing the LOB Interfaces

Table 6-2 and Table 6-3 compare the eight LOB programmatic interfaces by listing their functions and methods used to operate on LOBs. The tables are split in two simply to accommodate all eight interfaces. The functionality of the interfaces, with regards to LOBs, is described in the following sections.

Table 6-2 Comparing the LOB Interfaces, 1 of 2

PL/SQL: DBMS_LOB (dbmslob.sql) | C (OCI)(ociap.h) | C++ (OCCI) (occiData.h). Also for Clob and Bfile classes. | Pro*C/C++ and Pro*COBOL
--- | --- | --- | ---
DBMS_LOB.COMPARE | N/A | N/A | N/A
DBMS_LOB.INSTR | N/A | N/A | N/A
DBMS_LOB.SUBSTR | N/A | N/A | N/A
DBMS_LOB.APPEND | OCILobAppend() | Blob.append() | APPEND
N/A (use PL/SQL assign operator) | OCILobAssign() |  | ASSIGN
N/A | OCILobCharSetForm() | Clob.getCharsetForm (CLOB only) | N/A
N/A | OCILobCharSetId() | Clob.getCharsetId() (CLOB only) | N/A
DBMS_LOB.CLOSE | OCILobClose() | Blob.close() | CLOSE
N/A | N/A | Clob.closeStream() | N/A
DBMS_LOB.COPY | OCILobCopy2() | Blob.copy() | COPY
N/A | OCILobDisableBuffering() | N/A | DISABLE BUFFERING
N/A | OCILobEnableBuffering() | N/A | ENABLE BUFFERING
DBMS_LOB.ERASE | OCILobErase2() | N/A | ERASE
DBMS_LOB.FILECLOSE | OCILobFileClose() | Clob.close() | CLOSE
DBMS_LOB.FILECLOSEALL | OCILobFileCloseAll() | N/A | FILE CLOSE ALL
DBMS_LOB.FILEEXISTS | OCILobFileExist() | Bfile.fileExists() | DESCRIBE [FILEEXISTS]
DBMS_LOB.GETCHUNKSIZE | OCILobGetChunkSize() | Blob.getChunkSize() | DESCRIBE [CHUNKSIZE]
DBMS_LOB.FILEGETNAME | OCILobFileGetName() | Bfile.getFileName() and Bfile.getDirAlias() | DESCRIBE [DIRECTORY, FILENAME]
DBMS_LOB.FILEISOPEN | OCILobFileIsOpen() | Bfile.isOpen() | DESCRIBE [ISOPEN]
DBMS_LOB.FILEOPEN | OCILobFileOpen() | Bfile.open() | OPEN
N/A (use BFILENAME operator) | OCILobFileSetName() | Bfile.setName() | FILE SET
N/A | OCILobFlushBuffer() | N/A | FLUSH BUFFER
DBMS_LOB.GETLENGTH | OCILobGetLength2() | Blob.length() | DESCRIBE [LENGTH]
N/A | OCILobIsEqual() | Use operator = ( )=/!= | N/A
DBMS_LOB.ISOPEN | OCILobIsOpen() | Blob.isOpen() | DESCRIBE [ISOPEN]
DBMS_LOB.LOADFROMFILE | OCILobLoadFromFile2() | Use the overloadedcopy() method. | LOAD FROM FILE
N/A | OCILobLocatorIsInit() | Clob.isinitialized() | N/A
DBMS_LOB.OPEN | OCILobOpen() | Blob.open | OPEN
DBMS_LOB.READ | OCILobRead() | Blob.read | READ
DBMS_LOB.TRIM | OCILobTrim2() | Blob.trim | TRIM
DBMS_LOB.WRITE | OCILobWrite2 | Blob.write | WRITEORALOB.
DBMS_LOB.WRITEAPPEND | OCILobWriteAppend2() | N/A | WRITE APPEND
DBMS_LOB.CREATETEMPORARY | OCILobCreateTemporary() | N/A | N/A
DBMS_LOB.FREETEMPORARY | OCILobFreeTemporary() | N/A | N/A
DBMS_LOB.ISTEMPORARY | OCILobIsTemporary() | N/A | N/A
N/A | OCILobLocatorAssign() | use operator = ( ) or copy constructor | N/A

Table 6-3 Comparing the LOB Interfaces, 2 of 2

PL/SQL: DBMS_LOB (dbmslob.sql) | Visual Basic (OO4O) | Java (JDBC | OLEDB
--- | --- | --- | ---
DBMS_LOB.COMPARE | ORALOB.Compare | Use DBMS_LOB. | N/A
DBMS_LOB.INSTR | ORALOB.Matchpos | position | N/A
DBMS_LOB.SUBSTR | N/A | getBytes for BLOBs or BFILEs getSubString for CLOBs | N/A
DBMS_LOB.APPEND | ORALOB.Append | Use length and then putBytes or PutString | N/A
N/A [use PL/SQL assign operator] | ORALOB.Clone | N/A [use equal sign] | N/A
N/A | N/A | N/A | N/A
N/A | N/A | N/A | N/A
DBMS_LOB.CLOSE | N/A | use DBMS_LOB. | N/A
DBMS_LOB.COPY | ORALOB.Copy | Use read and write | N/A
N/A | ORALOB.DisableBuffering | N/A | N/A
N/A | ORALOB.EnableBuffering | N/A | N/A
DBMS_LOB.ERASE | ORALOB.Erase | Use DBMS_LOB. | N/A
DBMS_LOB.FILECLOSE | ORABFILE.Close | closeFile | N/A
DBMS_LOB.FILECLOSEALL | ORABFILE.CloseAll | Use DBMS_LOB. | N/A
DBMS_LOB.FILEEXISTS | ORABFILE.Exist | fileExists | N/A
DBMS_LOB.GETCHUNKSIZE | N/A | getChunkSize | N/A
DBMS_LOB.FILEGETNAME | ORABFILE.DirectoryName ORABFILE.FileName | getDirAlias getName | N/A
DBMS_LOB.FILEISOPEN | ORABFILE.IsOpen | Use DBMS_LOB.ISOPEN | N/A
DBMS_LOB.FILEOPEN | ORABFILE.Open | openFile | N/A
N/A (use BFILENAME operator) | DirectoryName FileName | Use BFILENAME | N/A
N/A | ORALOB.FlushBuffer | N/A | N/A
DBMS_LOB.GETLENGTH | ORALOB.Size | length | N/A
N/A | N/A | equals | N/A
DBMS_LOB.ISOPEN | ORALOB.IsOpen | use DBMS_LOB. IsOpen | N/A
DBMS_LOB.LOADFROMFILE | ORALOB. CopyFromBfile | Use read and then write | N/A
DBMS_LOB.OPEN | ORALOB.open | Use DBMS_LOB. | N/A
DBMS_LOB.READ | ORALOB.Read | BLOB or BFILE: getBytes and getBinaryStream CLOB: getString and getSubString and getCharacterStream | IRowset::GetData and ISequentialStream::Read
DBMS_LOB.TRIM | ORALOB.Trim | Use DBMS_LOB. | N/A
DBMS_LOB.WRITE | ORALOB.Write | BLOB: setBytes and setBinaryStream CLOB: setString and setCharacterStream | IRowsetChange::SetData and ISequentialStream::Write
DBMS_LOB.WRITEAPPEND | N/A | Use length and then putString or putBytes | N/A
DBMS_LOB.CREATETEMPORARY | N/A | N/A | N/A
DBMS_LOB.FREETEMPORARY | N/A | N/A | N/A
DBMS_LOB.ISTEMPORARY | N/A | N/A | N/A

## Using PL/SQL (DBMS_LOB Package) to Work with LOBs

The PL/SQL `DBMS_LOB` package can be used for the following operations:

- Internal persistent LOBs and Temporary LOBs: Read and modify operations, either entirely or in a piece-wise manner.

- BFILEs: Read operations

  See Also: Oracle Database PL/SQL Packages and Types Reference for detailed documentation, including parameters, parameter types, return values, and example code.

### Provide a LOB Locator Before Running the DBMS_LOB Routine

As described in more detail in the following, `DBMS_LOB` routines work based on LOB locators. For the successful completion of `DBMS_LOB` routines, you must provide an input locator representing a LOB that exists in the database tablespaces or external file system, before you call the routine.

- Persistent LOBs: First use SQL to define tables that contain LOB columns, and subsequently you can use SQL to initialize or populate the locators in these LOB columns.

- External LOB s: Define a `DIRECTORY` object that maps to a valid physical directory containing the external LOBs that you intend to access. These files must exist, and have READ permission for Oracle Server to process. If your operating system uses case-sensitive path names, then specify the directory in the correct case. See "Directory Objects" for more information.

Once the LOBs are defined and created, you may then `SELECT` a LOB locator into a local PL/SQL LOB variable and use this variable as an input parameter to `DBMS_LOB` for access to the LOB value.

Examples provided with each `DBMS_LOB` routine will illustrate this in the following sections.

### Guidelines for Offset and Amount Parameters in DBMS_LOB Operations

The following guidelines apply to offset and amount parameters used in procedures in the `DBMS_LOB` PL/SQL package:

- For character data—in all formats, fixed-width and varying-width—the `amount` and `offset` parameters are in characters. This applies to operations on `CLOB` and `NCLOB` datatypes.

- For binary data, the `offset` and `amount` parameters are in bytes. This applies to operations on `BLOB` datatypes.

- When using the following procedures:

  - `DBMS_LOB.LOADFROMFILE`

  - `DBMS_LOB.LOADBLOBFROMFILE`

  - `DBMS_LOB.LOADCLOBFROMFILE`

  you cannot specify an amount parameter with a value larger than the size of the `BFILE` you are loading from. To load the entire `BFILE` with these procedures, you must specify either the exact size of the `BFILE`, or the maximum allowable storage limit.

  See Also: "Loading a LOB with Data from a BFILE" "Loading a BLOB with Data from a BFILE" "Loading a CLOB or NCLOB with Data from a BFILE"

- When using `DBMS_LOB.READ`, the `amount` parameter can be larger than the size of the data. The amount should be less than or equal to the size of the buffer. The buffer size is limited to 32K.

  See Also: "Reading Data from a LOB"

### Determining Character Set ID

To determine the character set ID, you must know the character set name (a user can select from the `V$NLS_VALID_VALUES` view, which lists the names of the character sets that are valid as database and national character sets). Then call the function `NLS_CHARSET_ID` with the desired character set name as the one string argument. The character set ID is returned as an integer. `UTF16` does not work because it has no character set name. Use character set ID = 1000 for `UTF16`. Although `UTF16` is not allowed as a database or national character set, the APIs in `DBMS_LOB` support it for database conversion purposes. `DBMS_LOB.LOADCLOBFROMFILE` and other procedures in `DBMS_LOB` take character set ID, not character set name, as an input.

See Also: Oracle Database Globalization Support Guide, Appendix A, for supported languages

### PL/SQL Functions and Procedures for LOBs

PL/SQL functions and procedures that operate on `BLOB`s, `CLOB`s, `NCLOB`s, and `BFILE`s are summarized in the following:

- To modify persistent LOB values, see Table 6-4

- To read or examine LOB values, see Table 6-5

- To create, free, or check on temporary LOBs, see Table 6-6

- For read-only functions on external LOBs (`BFILE`s), see Table 6-7

- To open or close a LOB, or check if LOB is open, see Table 6-8

### PL/SQL Functions and Procedures to Modify LOB Values

Table 6-4 PL/SQL: DBMS_LOB Procedures to Modify LOB Values

Function/Procedure | Description
--- | ---
APPEND | Appends the LOB value to another LOB
CONVERTTOBLOB | Converts a `CLOB` to a `BLOB`
CONVERTTOCLOB | Converts a `BLOB` to a `CLOB`
COPY | Copies all or part of a LOB to another LOB
ERASE | Erases part of a LOB, starting at a specified offset
LOADFROMFILE | Load `BFILE` data into a persistent LOB
LOADCLOBFROMFILE | Load character data from a file into a LOB
LOADBLOBFROMFILE | Load binary data from a file into a LOB
TRIM | Trims the LOB value to the specified shorter length
WRITE | Writes data to the LOB at a specified offset
WRITEAPPEND | Writes data to the end of the LOB

### PL/SQL Functions and Procedures for Introspection of LOBs

Table 6-5 PL/SQL: DBMS_LOB Procedures to Read or Examine Internal and External LOB values

Function/Procedure | Description
--- | ---
COMPARE | Compares the value of two LOBs
GETCHUNKSIZE | Gets the chunk size used when reading and writing. This only works on persistent LOBs and does not apply to external LOBs (`BFILE`s).
GETLENGTH | Gets the length of the LOB value
INSTR | Returns the matching position of the nth occurrence of the pattern in the LOB
READ | Reads data from the LOB starting at the specified offset
SUBSTR | Returns part of the LOB value starting at the specified offset

### PL/SQL Operations on Temporary LOBs

Table 6-6 PL/SQL: DBMS_LOB Procedures to Operate on Temporary LOBs

Function/Procedure | Description
--- | ---
CREATETEMPORARY | Creates a temporary LOB
ISTEMPORARY | Checks if a LOB locator refers to a temporary LOB
FREETEMPORARY | Frees a temporary LOB

### PL/SQL Read-Only Functions and Procedures for BFILEs

Table 6-7 PL/SQL: DBMS_LOB Read-Only Procedures for BFILEs

Function/Procedure | Description
--- | ---
FILECLOSE | Closes the file. Use `CLOSE()` instead.
FILECLOSEALL | Closes all previously opened files
FILEEXISTS | Checks if the file exists on the server
FILEGETNAME | Gets the directory object name and file name
FILEISOPEN | `Checks if the file was opened using the input BFILE` locators. Use `ISOPEN()` instead.
FILEOPEN | Opens a file. Use `OPEN()` instead.

### PL/SQL Functions and Procedures to Open and Close Internal and External LOBs

Table 6-8 PL/SQL: DBMS_LOB Procedures to Open and Close Internal and External LOBs

Function/Procedure | Description
--- | ---
OPEN | Opens a LOB
ISOPEN | Sees if a LOB is open
CLOSE | Closes a LOB

These procedures are described in detail for specific LOB operations, such as, `INSERT` a row containing a LOB, in "Opening Persistent LOBs with the OPEN and CLOSE Interfaces".

## Using OCI to Work with LOBs

Oracle Call Interface (OCI) LOB APIs enable you to access and make changes to LOBs and read data from `BFILE`s in C. OCI functions for LOBs are discussed in greater detail later in this section.

### Setting the CSID Parameter for OCI LOB APIs

If you want to read or write data in 2 byteUunicode (UCS2) format, then set the `csid` (character set ID) parameter in `OCILobRead2()` and `OCILobWrite2()` to `OCI_UCS2ID`. The `csid` parameter indicates the character set id for the buffer parameter. You can set the `csid` parameter to any character set ID. If the `csid` parameter is set, then it will override the `NLS_LANG` environment variable.

See Also: Oracle Call Interface Programmer's Guide for information on the `OCIUnicodeToCharSet()` function and details on OCI syntax in general. Oracle Database Globalization Support Guide for detailed information about implementing applications in different languages.

### Fixed-Width and Varying-Width Character Set Rules for OCI

In OCI, for fixed-width client-side character sets, the following rules apply:

- `CLOB`s and `NCLOB`s: offset and amount parameters are always in characters

- `BLOB`s and `BFILE`s: offset and amount parameters are always in bytes

The following rules apply only to varying-width client-side character sets:

- Offset parameter: Regardless of whether the client-side character set is varying-width, the offset parameter is always as follows:

  - `CLOB`s and `NCLOB`s: in characters

  - `BLOB`s and `BFILE`s: in bytes

- Amount parameter: The amount parameter is always as follows:

  - When referring to a server-side LOB: in characters

  - When referring to a client-side buffer: in bytes

- OCILobFileGetLength(): Regardless of whether the client-side character set is varying-width, the output length is as follows:

  - `CLOB`s and `NCLOB`s: in characters

  - `BLOB`s and `BFILE`s: in bytes

- OCILobRead2(): With client-side character set of varying-width, `CLOB`s and `NCLOB`s:

  - I nput amount is in characters. Input amount refers to the number of characters to read from the server-side `CLOB` or `NCLOB`.

  - Output amount is in bytes. Output amount indicates how many bytes were read into the buffer `bufp`.

- OCILobWrite2(): With client-side character set of varying-width, `CLOB`s and `NCLOB`s:

  - Input amount is in bytes. The input amount refers to the number of bytes of data in the input buffer `bufp`.

  - Output amount is in characters. The output amount refers to the number of characters written into the server-side `CLOB` or `NCLOB`.

#### Other Operations

For all other LOB operations, irrespective of the client-side character set, the `amount` parameter is in characters for `CLOB`s and `NCLOB`s. These include `OCILobCopy2()`, `OCILobErase2()`, `OCILobLoadFromFile2()`, and `OCILobTrim2()`. All these operations refer to the amount of LOB data on the server.

See also: Oracle Database Globalization Support Guide

#### NCLOBs in OCI

`NCLOB`s are allowed as parameters in methods.

### OCILobLoadFromFile2() Amount Parameter

When using `OCILobLoadFromFile2()` you cannot specify `amount` larger than the length of the `BFILE`. To load the entire `BFILE`, you can pass the value returned by `OCILobGetStorageLimit()`.

### OCILobRead2() Amount Parameter

To read to the end of a LOB using `OCILobRead2()`, you specify an amount equal to the value returned by `OCILobGetStorageLimit()`. See "Reading Data from a LOB" for more information.

### OCILobLocator Pointer Assignment

Special care must be taken when assigning `OCILobLocator` pointers in an OCI program—using the "=" assignment operator. Pointer assignments create a shallow copy of the LOB. After the pointer assignment, the source and target LOBs point to the same copy of data.

These semantics are different from using LOB APIs, such as `OCILobAssign()` or `OCILobLocatorAssign()` to perform assignments. When the these APIs are used, the locators logically point to independent copies of data after assignment.

For temporary LOBs, before performing pointer assignments, you must ensure that any temporary LOB in the target LOB locator is freed by calling `OCIFreeTemporary()`. In contrast, when `OCILobLocatorAssign()` is used, the original temporary LOB in the target LOB locator variable, if any, is freed automatically before the assignment happens.

### LOB Locators in Defines and Out-Bind Variables in OCI

Before you reuse a LOB locator in a define or an out-bind variable in a SQL statement, you must free any temporary LOB in the existing LOB locator buffer using `OCIFreeTemporary()`.

### OCI Functions That Operate on BLOBs, CLOBs, NCLOBs, and BFILEs

OCI functions that operate on `BLOB`s, `CLOB`s, `NCLOB`s, and `BFILE`s are as follows:

- To modify persistent LOBs, see Table 6-9

- To read or examine LOB values, see Table 6-10

- To create or free temporary LOB, or check if Temporary LOB exists, see Table 6-11

- For read only functions on external LOBs (`BFILE`s), see Table 6-12

- To operate on LOB locators, see Table 6-13

- For LOB buffering, see Table 6-14

- To open and close LOBs, see Table 6-15

### OCI Functions to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values

Table 6-9 OCI Functions to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values

Function/Procedure | Description
--- | ---
`OCILobArrayWrite()` | Writes data using multiple locators in one round trip.
`OCILobAppend()` | Appends LOB value to another LOB.
`OCILobCopy2()` | Copies all or part of a LOB to another LOB.
`OCILobErase2()` | Erases part of a LOB, starting at a specified offset.
`OCILobLoadFromFile2()` | Loads `BFILE` data into a persistent LOB.
`OCILobTrim2()` | Truncates a LOB.
`OCILobWrite2()` | Writes data from a buffer into a LOB, overwriting existing data.
`OCILobWriteAppend2()` | Writes data from a buffer to the end of the LOB.

### OCI Functions to Read or Examine Persistent LOB and External LOB (BFILE) Values

Table 6-10 OCI Functions to Read or Examine persistent LOB and external LOB (BFILE) Values

Function/Procedure | Description
--- | ---
`OCILobArrayRead()` | Reads data using multiple locators in one round trip.
`OCILobGetChunkSize()` | Gets the Chunk size used when reading and writing. This works on persistent LOBs and does not apply to external LOBs (`BFILE`s).
`OCILobGetLength2()` | Returns the length of a LOB or a `BFILE`.
`OCILobRead2()` | Reads a specified portion of a non-`NULL` LOB or a `BFILE` into a buffer.

### OCI Functions for Temporary LOBs

Table 6-11 OCI Functions for Temporary LOBs

Function/Procedure | Description
--- | ---
`OCILobCreateTemporary()` | Creates a temporary LOB
`OCILobIsTemporary()` | Sees if a temporary LOB exists
`OCILobFreeTemporary()` | Frees a temporary LOB

### OCI Read-Only Functions for BFILEs

Table 6-12 OCI Read-Only Functions for BFILES

Function/Procedure | Description
--- | ---
`OCILobFileClose()` | Closes an open `BFILE`.
`OCILobFileCloseAll()` | Closes all open `BFILE`s.
`OCILobFileExists()` | Checks whether a `BFILE` exists.
`OCILobFileGetName()` | Returns the name of a `BFILE`.
`OCILobFileIsOpen()` | Checks whether a `BFILE` is open.
`OCILobFileOpen()` | Opens a `BFILE`.

### OCI LOB Locator Functions

Table 6-13 OCI LOB-Locator Functions

Function/Procedure | Description
--- | ---
`OCILobAssign()` | Assigns one LOB locator to another.
`OCILobCharSetForm()` | Returns the character set form of a LOB.
`OCILobCharSetId()` | Returns the character set ID of a LOB.
`OCILobFileSetName()` | Sets the name of a `BFILE` in a locator.
`OCILobIsEqual()` | Checks whether two LOB locators refer to the same LOB.
`OCILobLocatorIsInit()` | Checks whether a LOB locator is initialized.

### OCI LOB-Buffering Functions

Table 6-14 OCI LOB-Buffering Functions

Function/Procedure | Description
--- | ---
`OCILobDisableBuffering()` | Disables the buffering subsystem use.
`OCILobEnableBuffering()` | Uses the LOB buffering subsystem for subsequent reads and writes of LOB data.
`OCILobFlushBuffer()` | Flushes changes made to the LOB buffering subsystem to the database (server)

### OCI Functions to Open and Close Internal and External LOBs

Table 6-15 OCI Functions to Open and Close Internal and External LOBs

Function/Procedure | Description
--- | ---
`OCILobOpen()` | Opens a LOB
`OCILobIsOpen()` | Sees if a LOB is open
`OCILobClose()` | Closes a LOB

### OCI LOB Examples

Further OCI examples are provided in:

- Chapter 14, " LOB APIs for Basic Operations"

- Chapter 15, "LOB APIs for BFILE Operations"

See also Appendix B, "OCI Demonstration Programs" in Oracle Call Interface Programmer's Guide, for further OCI demonstration script listings.

### Further Information About OCI

For further information and features of OCI, refer to the OTN Web site, `http://www.oracle.com/technology/` for OCI features and FAQs.

## Using C++ (OCCI) to Work with LOBs

Oracle C++ Call Interface (OCCI) is a C++ API for manipulating data in an Oracle database. OCCI is organized as an easy-to-use set of C++ classes that enable a C++ program to connect to a database, run SQL statements, insert/update values in database tables, retrieve results of a query, run stored procedures in the database, and access metadata of database schema objects. OCCI also provides a seamless interface to manipulate objects of user-defined types as C++ class instances.

Oracle C++ Call Interface (OCCI) is designed so that you can use OCI and OCCI together to build applications.

The OCCI API provides the following advantages over JDBC and ODBC:

- OCCI encompasses more Oracle functionality than JDBC. OCCI provides all the functionality of OCI that JDBC does not provide.

- OCCI provides compiled performance. With compiled programs, the source code is already written as close to the computer as possible. Because JDBC is an interpreted API, it cannot provide the performance of a compiled API. With an interpreted program, performance degrades as each line of code must be interpreted individually into code that is close to the computer.

- OCCI provides memory management with smart pointers. You do not have to be concerned about managing memory for OCCI objects. This results in robust higher performance application code.

- Navigational access of OCCI enables you to intuitively access objects and call methods. Changes to objects persist without need to write corresponding SQL statements. If you use the client side cache, then the navigational interface performs better than the object interface.

- With respect to ODBC, the OCCI API is simpler to use. Because ODBC is built on the C language, OCCI has all the advantages C++ provides over C. Moreover, ODBC has a reputation as being difficult to learn. The OCCI, by contrast, is designed for ease of use.

You can use OCCI to make changes to an entire persistent LOB, or to pieces of the beginning, middle, or end of it, as follows:

- For reading from internal and external LOBs (`BFILE`s)

- For writing to persistent LOBs

### OCCI Classes for LOBs

OCCI provides the following classes that allow you to use different types of LOB instances as objects in your C++ application:

- `Clob` class to access and modify data stored in internal `CLOB`s and `NCLOB`s

- `Blob` class to access and modify data stored in internal `BLOB`s

- `Bfile` class to access and read data stored in external LOBs (`BFILE`s)

  See Also: Syntax information on these classes and details on OCCI in general is available in the Oracle C++ Call Interface Programmer's Guide.

#### Clob Class

The Clob driver implements a `CLOB` object using an SQL LOB locator. This means that a CLOB object contains a logical pointer to the SQL `CLOB` data rather than the data itself.

The `CLOB` interface provides methods for getting the length of an SQL `CLOB` value, for materializing a `CLOB` value on the client, and getting a substring. Methods in the `ResultSet` and `Statement` interfaces such as `getClob()` and `setClob()` allow you to access SQL `CLOB` values.

See Also: Oracle C++ Call Interface Programmer's Guide for detailed information on the Clob class.

#### Blob Class

Methods in the `ResultSet` and `Statement` interfaces, such as `getBlob()` and `setBlob()`, allow you to access SQL `BLOB` values. The `Blob` interface provides methods for getting the length of a SQL `BLOB` value, for materializing a `BLOB` value on the client, and for extracting a part of the `BLOB`.

See Also: Oracle C++ Call Interface Programmer's Guide for detailed information on the Blob class methods and details on instantiating and initializing a Blob object in your C++ application. Oracle Database Globalization Support Guide for detailed information about implementing applications in different languages.

#### Bfile Class

The `Bfile` class enables you to instantiate a `Bfile` object in your C++ application. You must then use methods of the `Bfile` class, such as the `setName()` method, to initialize the `Bfile` object which associates the object properties with an object of type `BFILE` in a `BFILE` column of the database.

See Also: Oracle C++ Call Interface Programmer's Guide for detailed information on the `Blob` class methods and details on instantiating and initializing an `Blob` object in your C++ application.

### Fixed-Width Character Set Rules

In OCCI, for fixed-width client-side character sets, the following rules apply:

- `Clob`: offset and amount parameters are always in characters

- `Blob`: offset and amount parameters are always in bytes

- `Bfile`: offset and amount parameters are always in bytes

### Varying-Width Character Set Rules

The following rules apply only to varying-width client-side character sets:

- Offset parameter: Regardless of whether the client-side character set is varying-width, the offset parameter is always as follows:

  - `Clob()`: in characters

  - `Blob()`: in bytes

  - `Bfile()`: in bytes

- Amount parameter: The amount parameter is always as follows:

  - `Clob`: in characters, when referring to a server-side LOB

  - `Blob`: in bytes, when referring to a client-side buffer

  - `Bfile`: in bytes, when referring to a client-side buffer

- length(): Regardless of whether the client-side character set is varying-width, the output length is as follows:

  - `Clob.length()`: in characters

  - `Blob.length()`: in bytes

  - `Bfile.length()`: in bytes

- Clob.read() and Blob.read(): With client-side character set of varying-width, `CLOB`s and `NCLOB`s:

  - I nput amount is in characters. Input amount refers to the number of characters to read from the server-side `CLOB` or `NCLOB`.

  - Output amount is in bytes. Output amount indicates how many bytes were read into the OCCI buffer parameter, `buffer`.

- Clob.write() and Blob.write(): With client-side character set of varying-width, `CLOB`s and `NCLOB`s:

  - Input amount is in bytes. Input amount refers to the number of bytes of data in the OCCI input buffer, `buffer`.

  - O utput amount is in characters. Output amount refers to the number of characters written into the server-side `CLOB` or `NCLOB`.

### Offset and Amount Parameters for Other OCCI Operations

For all other OCCI LOB operations, irrespective of the client-side character set, the amount parameter is in characters for `CLOB`s and `NCLOB`s. These include the following:

- `Clob.copy()`

- `Clob.erase()`

- `Clob.trim()`

- For LoadFromFile functionality, overloaded `Clob.copy()`

All these operations refer to the amount of LOB data on the server.

See also: Oracle Database Globalization Support Guide

#### NCLOBs in OCCI

- `NCLOB` instances are allowed as parameters in methods

- `NCLOB` instances are allowed as attributes in object types.

### Amount Parameter for OCCI LOB copy() Methods

The `copy()` method on `Clob` and `Blob` enables you to load data from a `BFILE`. You can pass one of the following values for the `amount` parameter to this method:

- An amount smaller than the size of the `BFILE` to load a portion of the data

- An amount equal to the size of the `BFILE` to load all of the data

- The `UB4MAXVAL` constant to load all of the `BFILE` data

You cannot specify an amount larger than the length of the `BFILE`.

### Amount Parameter for OCCI read() Operations

The `read()` method on an `Clob`, `Blob`, or `Bfile` object, reads data from a `BFILE`. You can pass one of the following values for the amount parameter to specify the amount of data to read:

- An amount smaller than the size of the `BFILE` to load a portion of the data

- An amount equal to the size of the `BFILE` to load all of the data

- `0` (zero) to read until the end of the `BFILE` in streaming mode

You cannot specify an amount larger than the length of the `BFILE`.

### Further Information About OCCI

See Also: Oracle C++ Call Interface Programmer's Guide `http://www.oracle.com/` search for articles and product information featuring OCCI.

### OCCI Methods That Operate on BLOBs, BLOBs, NCLOBs, and BFILEs

OCCI methods that operate on `BLOB`s, `CLOB`s, `NCLOB`s, and `BFILE`s are as follows:

- To modify persistent LOBs, see Table 6-16

- To read or examine LOB values, see Table 6-17

- For read only methods on external LOBs (`BFILE`s), see Table 6-18

- Other LOB OCCI methods are described in Table 6-19

- To open and close LOBs, see Table 6-20

### OCCI Methods to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values

Table 6-16 OCCI Clob and Blob Methods to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values

Function/Procedure | Description
--- | ---
`Blob/Clob.append()` | Appends `CLOB or BLOB` value to another LOB.
`Blob/Clob.copy()` | Copies all or part of a `CLOB` or `BLOB` to another LOB.
`Blob/Clob.copy()` | Loads `BFILE` data into a persistent LOB.
`Blob/Clob.trim()` | Truncates a `CLOB or BLOB`.
`Blob/Clob.write()` | Writes data from a buffer into a LOB, overwriting existing data.

### OCCI Methods to Read or Examine Persistent LOB and BFILE Values

Table 6-17 OCCI Blob/Clob/Bfile Methods to Read or Examine persistent LOB and BFILE Values

Function/Procedure | Description
--- | ---
`Blob/Clob.getChunkSize()` | Gets the Chunk size used when reading and writing. This works on persistent LOBs and does not apply to external LOBs (`BFILE`s).
`Blob/Clob.length()` | Returns the length of a LOB or a `BFILE`.
`Blob/Clob.read()` | Reads a specified portion of a non-`NULL` LOB or a `BFILE` into a buffer.

### OCCI Read-Only Methods for BFILEs

Table 6-18 OCCI Read-Only Methods for BFILES

Function/Procedure | Description
--- | ---
`Bfile.close()` | Closes an open `BFILE`.
`Bfile.fileExists()` | Checks whether a `BFILE` exists.
`Bfile.getFileName()` | Returns the name of a `BFILE`.
`Bfile.getDirAlias()` | Gets the directory object name.
`Bfile.isOpen()` | Checks whether a `BFILE` is open.
`Bfile.open()` | Opens a `BFILE`.

### Other OCCI LOB Methods

Table 6-19 Other OCCI LOB Methods

Methods | Description
--- | ---
`Clob/Blob/Bfile.operator=()` | Assigns one LOB locator to another. Use = or the copy constructor.
`Clob.getCharSetForm()` | Returns the character set form of a LOB.
`Clob.getCharSetId()` | Returns the character set ID of a LOB.
`Bfile.setName()` | Sets the name of a `BFILE`.
`Clob/Blob/Bfile.operator==()` | Checks whether two LOB refer to the same LOB.
`Clob/Blob/Bfile.isInitialized()` | Checks whether a LOB is initialized.

### OCCI Methods to Open and Close Internal and External LOBs

Table 6-20 OCCI Methods to Open and Close Internal and External LOBs

Function/Procedure | Description
--- | ---
`Clob/Blob/Bfile.Open()` | Opens a LOB
`Clob/Blob/Bfile.isOpen()` | Sees if a LOB is open
`Clob/Blob/Bfile.Close()` | Closes a LOB

## Using C/C++ (Pro*C) to Work with LOBs

You can make changes to an entire persistent LOB, or to pieces of the beginning, middle or end of a LOB by using embedded SQL. You can access both internal and external LOBs for read purposes, and you can write to persistent LOBs.

Embedded SQL statements allow you to access data stored in `BLOB`s, `CLOB`s, `NCLOB`s, and `BFILE`s. These statements are listed in the following tables, and are discussed in greater detail later in the chapter.

See Also: Pro*C/C++ Programmer's Guide for detailed documentation, including syntax, host variables, host variable types and example code.

### First Provide an Allocated Input Locator Pointer That Represents LOB

Unlike locators in PL/SQL, locators in Pro*C/C++ are mapped to locator pointers which are then used to refer to the LOB or BFILE value.

To successfully complete an embedded SQL LOB statement you must do the following:

1. Provide an allocated input locator pointer that represents a LOB that exists in the database tablespaces or external file system before you run the statement.

2. SELECT a LOB locator into a LOB locator pointer variable

3. Use this variable in the embedded SQL LOB statement to access and manipulate the LOB value

   See Also: APIs for supported LOB operations are described in detail in: Chapter 12, "Operations Specific to Persistent and Temporary LOBs" Chapter 14, " LOB APIs for Basic Operations" Chapter 15, "LOB APIs for BFILE Operations"

### Pro*C/C++ Statements That Operate on BLOBs, CLOBs, NCLOBs, and BFILEs

Pro*C statements that operate on BLOBs, CLOBs, and NCLOBs are listed in the following tables:

- To modify persistent LOBs, see Table 6-21

- To read or examine LOB values, see Table 6-22

- To create or free temporary LOB, or check if Temporary LOB exists, see Table 6-23

- To operate close and 'see if file exists' functions on BFILEs, see Table 6-24

- To operate on LOB locators, see Table 6-25

- For LOB buffering, see Table 6-26

- To open or close LOBs or `BFILE`s, see Table 6-27

### Pro*C/C++ Embedded SQL Statements to Modify Persistent LOB Values

Table 6-21 Pro*C/C++: Embedded SQL Statements to Modify Persistent LOB Values

Statement | Description
--- | ---
APPEND | Appends a LOB value to another LOB.
COPY | Copies all or a part of a LOB into another LOB.
ERASE | Erases part of a LOB, starting at a specified offset.
LOAD FROM FILE | Loads `BFILE` data into a persistent LOB at a specified offset.
TRIM | Truncates a LOB.
WRITE | Writes data from a buffer into a LOB at a specified offset.
WRITE APPEND | Writes data from a buffer into a LOB at the end of the LOB.

### Pro*C/C++ Embedded SQL Statements for Introspection of LOBs

Table 6-22 Pro*C/C++: Embedded SQL Statements for Introspection of LOBs

Statement | Description
--- | ---
DESCRIBE [CHUNKSIZE] | Gets the Chunk size used when writing. This works for persistent LOBs only. It does not apply to external LOBs (`BFILE`s).
DESCRIBE [LENGTH] | Returns the length of a LOB or a `BFILE`.
READ | reads a specified portion of a non-`NULL` LOB or a `BFILE` into a buffer.

### Pro*C/C++ Embedded SQL Statements for Temporary LOBs

Table 6-23 Pro*C/C++: Embedded SQL Statements for Temporary LOBs

Statement | Description
--- | ---
CREATE TEMPORARY | Creates a temporary LOB.
DESCRIBE [ISTEMPORARY] | Sees if a LOB locator refers to a temporary LOB.
FREE TEMPORARY | Frees a temporary LOB.

### Pro*C/C++ Embedded SQL Statements for BFILEs

Table 6-24 Pro*C/C++: Embedded SQL Statements for BFILES

Statement | Description
--- | ---
FILE CLOSE ALL | Closes all open `BFILE`s.
DESCRIBE [FILEEXISTS] | Checks whether a `BFILE` exists.
DESCRIBE [DIRECTORY,FILENAME] | Returns the directory object name and filename of a `BFILE`.

### Pro*C/C++ Embedded SQL Statements for LOB Locators

Table 6-25 Pro*C/C++ Embedded SQL Statements for LOB Locators

Statement | Description
--- | ---
ASSIGN | Assigns one LOB locator to another.
FILE SET | Sets the directory object name and filename of a `BFILE` in a locator.

### Pro*C/C++ Embedded SQL Statements for LOB Buffering

Table 6-26 Pro*C/C++ Embedded SQL Statements for LOB Buffering

Statement | Description
--- | ---
DISABLE BUFFERING | Disables the use of the buffering subsystem.
ENABLE BUFFERING | Uses the LOB buffering subsystem for subsequent reads and writes of LOB data.
FLUSH BUFFER | Flushes changes made to the LOB buffering subsystem to the database (server)

### Pro*C/C++ Embedded SQL Statements to Open and Close LOBs

Table 6-27 Pro*C/C++ Embedded SQL Statements to Open and Close Persistent LOBs and External LOBs (BFILEs)

Statement | Description
--- | ---
OPEN | Opens a LOB or `BFILE`.
DESCRIBE [ISOPEN] | Sees if a LOB or `BFILE` is open.
CLOSE | Closes a LOB or `BFILE`.

## Using COBOL (Pro*COBOL) to Work with LOBs

You can make changes to an entire persistent LOB, or to pieces of the beginning, middle or end of it by using embedded SQL. You can access both internal and external LOBs for read purposes, and you can also write to persistent LOBs.

Embedded SQL statements allow you to access data stored in `BLOB`s, `CLOB`s, `NCLOB`s, and `BFILE`s. These statements are listed in the following tables, and are discussed in greater detail later in the manual.

### First Provide an Allocated Input Locator Pointer That Represents LOB

Unlike locators in PL/SQL, locators in Pro*COBOL are mapped to locator pointers which are then used to refer to the LOB or BFILE value. For the successful completion of an embedded SQL LOB statement you must perform the following:

1. Provide an allocated input locator pointer that represents a LOB that exists in the database tablespaces or external file system before you run the statement.

2. SELECT a LOB locator into a LOB locator pointer variable

3. Use this variable in an embedded SQL LOB statement to access and manipulate the LOB value.

   See Also: APIs for supported LOB operations are described in detail in: Chapter 12, "Operations Specific to Persistent and Temporary LOBs" Chapter 14, " LOB APIs for Basic Operations" Chapter 15, "LOB APIs for BFILE Operations"

Where the Pro*COBOL interface does not supply the required functionality, you can call OCI using C. Such an example is not provided here because such programs are operating system dependent.

See Also: Pro*COBOL Programmer's Guide for detailed documentation, including syntax, host variables, host variable types, and example code.

### Pro*COBOL Statements That Operate on BLOBs, CLOBs, NCLOBs, and BFILEs

The following Pro*COBOL statements operate on BLOBs, CLOBs, NCLOBs, and BFILEs:

- To modify persistent LOBs, see Table 6-28

- To read or examine internal and external LOB values, see Table 6-29

- To create or free temporary LOB, or check LOB locator, see Table 6-30

- To operate close and 'see if file exists' functions on `BFILE`s, see Table 6-31

- To operate on LOB locators, see Table 6-32

- For LOB buffering, see Table 6-33

- To open or close persistent LOBs or `BFILE`s, see Table 6-34

### Pro*COBOL Embedded SQL Statements to Modify Persistent LOB Values

Table 6-28 Pro*COBOL Embedded SQL Statements to Modify LOB Values

Statement | Description
--- | ---
APPEND | Appends a LOB value to another LOB.
COPY | Copies all or part of a LOB into another LOB.
ERASE | Erases part of a LOB, starting at a specified offset.
LOAD FROM FILE | Loads `BFILE` data into a persistent LOB at a specified offset.
TRIM | Truncates a LOB.
WRITE | Writes data from a buffer into a LOB at a specified offset
WRITE APPEND | Writes data from a buffer into a LOB at the end of the LOB.

### Pro*COBOL Embedded SQL Statements for Introspection of LOBs

Table 6-29 Pro*COBOL Embedded SQL Statements for Introspection of LOBs

Statement | Description
--- | ---
DESCRIBE [CHUNKSIZE] | Gets the Chunk size used when writing.
DESCRIBE [LENGTH] | Returns the length of a LOB or a `BFILE`.
READ | Reads a specified portion of a non-`NULL` LOB or a `BFILE` into a buffer.

### Pro*COBOL Embedded SQL Statements for Temporary LOBs

Table 6-30 Pro*COBOL Embedded SQL Statements for Temporary LOBs

Statement | Description
--- | ---
CREATE TEMPORARY | Creates a temporary LOB.
DESCRIBE [ISTEMPORARY] | Sees if a LOB locator refers to a temporary LOB.
FREE TEMPORARY | Frees a temporary LOB.

### Pro*COBOL Embedded SQL Statements for BFILEs

Table 6-31 Pro*COBOL Embedded SQL Statements for BFILES

Statement | Description
--- | ---
FILE CLOSE ALL | Closes all open `BFILE`s.
DESCRIBE [FILEEXISTS] | Checks whether a `BFILE` exists.
DESCRIBE [DIRECTORY, FILENAME] | Returns the directory object name and filename of a `BFILE`.

### Pro*COBOL Embedded SQL Statements for LOB Locators

Table 6-32 Pro*COBOL Embedded SQL Statements for LOB Locator Statements

Statement | Description
--- | ---
ASSIGN | Assigns one LOB locator to another.
FILE SET | Sets the directory object name and filename of a `BFILE` in a locator.

### Pro*COBOL Embedded SQL Statements for LOB Buffering

Table 6-33 Pro*COBOL Embedded SQL Statements for LOB Buffering

Statement | Description
--- | ---
DISABLE BUFFERING | Disables the use of the buffering subsystem.
ENABLE BUFFERING | Uses the LOB buffering subsystem for subsequent reads and writes of LOB data.
FLUSH BUFFER | Flushes changes made to the LOB buffering subsystem to the database (server)

### Pro*COBOL Embedded SQL Statements for Opening and Closing LOBs and BFILEs

Table 6-34 Pro*COBOL Embedded SQL Statements for Opening and CLosing Persistent LOBs and BFILEs

Statement | Description
--- | ---
OPEN | Opens a LOB or `BFILE`.
DESCRIBE [ISOPEN] | Sees if a LOB or `BFILE` is open.
CLOSE | Closes a LOB or `BFILE`.

## Using Visual Basic (Oracle Objects for OLE) to Work with LOBs

Oracle Objects for OLE (OO4O) is a set of programmable COM objects that simplifies the development of applications designed to communicate with an Oracle Database. OO4O offers high performance database access. It also provides easy access to features unique to Oracle, yet otherwise cumbersome or inefficient to use from other ODBC or OLE DB-based components, such as ADO.

You can make changes to an entire persistent LOB, or to pieces of the beginning, middle or end of it, with the Oracle Objects for OLE (OO4O) API, by using one of the following objects interfaces:

- OraBlob`:` To provide methods for performing operations on BLOB datatypes in the database

- OraClob`:` To provide methods for performing operations on CLOB datatypes in the database

- OraBFile`:` To provide methods for performing operations on BFILE data stored in operating system files.

  Note: OracleBlob and OracleClob have been deprecated and no longer work

### OO4O Syntax Reference

The OO4O syntax reference and further information is viewed from the OO4O online help. Oracle Objects for OLE (OO4O), a Windows-based product included with the database, has no manuals, only online help.

Its online help is available through the Application Development submenu of the database installation. To view specific methods and properties from the Help Topics menu, select the Contents tab > OO4O Automation Server > Methods or Properties.

For further information about OO4O, refer to the following Web site:

- `http://www.oracle.com/technology/index.html`

  Select Products > Internet Tools > Programmer. Scroll down to "Oracle Objects for OLE". At the bottom of the page is a list of useful articles for using the interfaces.

- `http://www.oracle.com/` Search for articles on OO4O or Oracle Objects for OLE.

### OraBlob, OraClob, and OraBfile Object Interfaces Encapsulate Locators

These interfaces encapsulate LOB locators, so you do not deal directly with locators, but instead, can use methods and properties provided to perform operations and get state information.

#### OraBlob and OraClob Objects Are Retrieved as Part of Dynaset

When `OraBlob` and `OraClob` objects are retrieved as a part of a dynaset, these objects represent LOB locators of the dynaset current row. If the dynaset current row changes due to a move operation, then the `OraBlob` and `OraClob` objects represent the LOB locator for the new current row.

#### Use the Clone Method to Retain Locator Independent of the Dynaset Move

To retain the LOB locator of the `OraBlob` and `OraClob` object independent of the dynaset move operation, use the `Clone` method. This method returns the `OraBlob` and `OraClob` object. You can also use these objects as PL/SQL bind parameters.

### Example of OraBlob and OraBfile

The following example shows usage of OraBlob and OraBfile.

    Dim OraDyn as OraDynaset, OraSound1 as OraBLOB, OraSoundClone as OraBlob, OraMyBfile as OraBFile

    OraConnection.BeginTrans
    set OraDyn = OraDb.CreateDynaset("select * from print_media order by product_id", ORADYN_DEFAULT)
    set OraSound1 = OraDyn.Fields("Sound").value
    set OraSoundClone = OraSound1

    OraParameters.Add "id", 1,ORAPARAM_INPUT
    OraParameters.Add "mybfile", Empty,ORAPARAM_OUTPUT
    OraParameters("mybfile").ServerType = ORATYPE_BFILE

    OraDatabase.ExecuteSQL ("begin  GetBFile(:id, :mybfile ") end")

    Set OraMyBFile = OraParameters("mybfile").value
    'Go to Next row
    OraDyn.MoveNext

    OraDyn.Edit
    'Lets update OraSound1 data with that from the BFILE
    OraSound1.CopyFromBFile  OraMyBFile
    OraDyn.Update

    OraDyn.MoveNext
    'Go to Next row
    OraDyn.Edit
    'Lets update OraSound1 by appending with LOB data from 1st row represenetd by
    'OraSoundClone
    OraSound1.Append  OraSoundClone
    OraDyn.Update

    OraConnection.CommitTrans

In the preceding example:

`OraSound1` — represents the locator for the current row in the dynaset `OraSoundClone` — represents the locator for the 1st row.

A change in the current row (say a `OraDyn.MoveNext`) means the following:

`OraSound1` — will represent the locator for the 2nd row.

`OraSoundClone —` will represent the locator in the 1st row. `OraSoundClone` only refers the locator for the 1st row irrespective of any `OraDyn` row navigation).

`OraMyBFile` — refers to the locator obtained from an PL/SQL "OUT" parameter as a result of executing a PL/SQL procedure, either by doing an `OraDatabase`.`ExecuteSQL`.

Note: A LOB obtained by executing SQL is only valid for the duration of the transaction. For this reason, "BEGINTRANS" and "COMMITTRANS" are used to specify the duration of the transaction.

### OO4O Methods and Properties to Access Data Stored in LOBs

Oracle Objects for OLE (OO4O) includes methods and properties that you can use to access data stored in `BLOB`s, `CLOB`s, `NCLOB`s, and `BFILE`s.

See Also: APIs for supported LOB operations are described in detail in: Chapter 12, "Operations Specific to Persistent and Temporary LOBs" Chapter 14, " LOB APIs for Basic Operations" Chapter 15, "LOB APIs for BFILE Operations"

See Also: The OO4O online help for detailed information including parameters, parameter types, return values, and example code. Oracle Objects for OLE (OO4O), a Windows-based product included with the database, has no manuals, only online help. The OO4O online help is available through the Application Development submenu of the database installation.

The following OO4O methods and properties operate on `BLOB`s, `CLOB`s, `NCLOB`s, and `BFILE`s:

- To modify persistent LOBs, see Table 6-35

- To read or examine internal and external LOB values, see Table 6-36

- To open and close `BFILE`s, see Table 6-37

- For LOB buffering, see Table 6-38

- Properties such as to see if LOB is `NULL`, or to get or set polling amount, see Table 6-39

- For read-only `BFILE` methods, see Table 6-40

- For BFILE properties, see Table 6-41

### OO4O Methods to Modify BLOB, CLOB, and NCLOB Values

Table 6-35 OO4O Methods to Modify BLOB, CLOB, and NCLOB Values

Methods | Description
--- | ---
`OraBlob.Append` `OraClob.Append` | Appends `BLOB` value to another LOB. Appends `CLOB` or `NCLOB` value to another LOB.
`OraBlob.Copy` `OraClob.Copy` | Copies a portion of a `BLOB` into another LOB Copies a portion of a `CLOB` or `NCLOB` into another LOB
`OraBlob.Erase` `OraClob.Erase` | Erases part of a `BLOB`, starting at a specified offset Erases part of a `CLOB` or `NCLOB`, starting at a specified offset
`OraBlob.CopyFromBFile` `OraClob.CopyFromBFile` | Loads `BFILE` data into an internal `BLOB` Loads `BFILE` data into an internal `CLOB` or `NCLOB`
`OraBlob.Trim` `OraClob.Trim` | Truncates a `BLOB` Truncates a `CLOB or NCLOB`
`OraBlob.CopyFromFile` `OraClob.CopyFromFile` | Writes data from a file to a `BLOB` Writes data from a file to a `CLOB` or `NCLOB`
`OraBlob.Write` `OraClob.Write` | Writes data to the `BLOB` Writes data to the `CLOB` or `NCLOB`

### OO4O Methods to Read or Examine Internal and External LOB Values

Table 6-36 OO4O Methods to Read or Examine Internal and External LOB Values

Function/Procedure | Description
--- | ---
`OraBlob.Read` `OraClob.Read` `OraBFile.Read` | Reads a specified portion of a non-`NULL` `BLOB` into a buffer Reads a specified portion of a non-`NULL` `CLOB` into a buffer Reads a specified portion of a non-`NULL` `BFILE` into a buffer
`OraBlob.CopyToFile` `OraClob.CopyToFile` | Reads a specified portion of a non-`NULL` `BLOB` to a file Reads a specified portion of a non-`NULL` `CLOB` to a file

### OO4O Methods to Open and Close External LOBs (BFILEs)

Table 6-37 OO4O Methods to Open and Close External LOBs (BFILEs)

Method | Description
--- | ---
`OraBFile.Open` | Opens `BFILE`.
`OraBFile.Close` | Closes `BFILE`.

### OO4O Methods for Persistent LOBBuffering

Table 6-38 OO4O Methods for Persistent LOB Buffering

Method | Description
--- | ---
`OraBlob.FlushBuffer` `OraClob.FlushBuffer` | Flushes changes made to the `BLOB` buffering subsystem to the database Flushes changes made to the `CLOB` buffering subsystem to the database
`OraBlob.EnableBuffering` `OraClob.EnableBuffering` | Enables buffering of `BLOB` operations Enables buffering of `CLOB` operations
`OraBlob.DisableBuffering` `OraClob.DisableBuffering` | Disables buffering of `BLOB` operations Disables buffering of `CLOB` operations

### OO4O Properties for Operating on LOBs

Table 6-39 OO4O Properties for Operating on LOBs

Property | Description
--- | ---
`IsNull (Read)` | Indicates when a LOB is `NULL`
`PollingAmount(Read/Write)` | Gets/Sets total amount for Read/Write polling operation
`Offset(Read/Write)` | Gets/Sets offset for Read/Write operation. By default, it is set to 1.
`Status(Read)` | Returns the polling status.Possible values are `ORALOB_NEED_DATA` There is more data to be read or written `ORALOB_NO_DATA` There is no more data to be read or written `ORALOB_SUCCESS_LOB` data read/written successfully
`Size(Read)` | Returns the length of the LOB data

### OO4O Read-Only Methods for External Lobs (BFILEs)

Table 6-40 OO4O Read-Only Methods for External LOBs (BFILEs)

Methods | Description
--- | ---
`OraBFile.Close` | Closes an open `BFILE`
`OraBFile.CloseAll` | Closes all open `BFILE`s
`OraBFile.Open` | Opens a `BFILE`
`OraBFile.IsOpen` | Determines if a `BFILE` is open

### OO4O Properties for Operating on External LOBs (BFILEs)

Table 6-41 OO4O Properties for Operating on External LOBs (BFILEs)

Property | Description
--- | ---
`OraBFile.DirectoryName` | Gets/Sets the server side directory object name.
`OraBFile.FileName(Read/Write)` | Gets/Sets the server side filename.
`OraBFile.Exists` | Checks whether a `BFILE` exists.

## Using Java (JDBC) to Work with LOBs

You can perform the following tasks on LOBs with Java (JDBC):

- Modifying Internal Persistent LOBs Using Java

- Reading Internal Persistent LOBs and External LOBs (BFILEs) with Java

- Calling DBMS_LOB Package from Java (JDBC)

- Referencing LOBs Using Java (JDBC)

- Create and Manipulate Temporary LOBs and Store Them in Tables as Permanent LOBs. See JDBC Temporary LOB APIs

### Modifying Internal Persistent LOBs Using Java

You can make changes to an entire persistent LOB, or to pieces of the beginning, middle, or end of a persistent LOB in Java by means of the JDBC API using the classes:

- `oracle`.`sql`.`BLOB`

- `oracle`.`sql`.`CLOB`

These classes implement `java.sql.Blob` and `java.sql.Clob` interfaces according to the JDBC 3.0 specification, which has methods for LOB modification. They also include legacy Oracle proprietary methods for LOB modification. These legacy methods are marked as deprecated and may be removed in a future release.

If you use JDK 1.4 or higher, then you can use variables typed `java.sql.Blob` and `java.sql.Clob`.

The JDBC 3.0 methods are included in `classes12.jar`, so that they can be used in JDK 1.2 or 1.3, but since they are not part of the `java.sql.Blob` and `java.sql.Clob` interfaces in those JDK versions, you must use variables typed or cast to `oracle.sql.BLOB` or `oracle.sql.CLOB`.

Table 6-42 and Table 6-43 show the conversions between Oracle proprietary methods that have been deprecated and JDBC 3.0 standard methods.

Table 6-42 BLOB Method Equivalents

Oracle Proprietary Method (Deprecated) | JDBC 3.0 Standard Method Replacement
--- | ---
putBytes(long pos, byte [] bytes) | setBytes(long pos, byte[] bytes)
putBytes(long pos, byte [] bytes, int length) | setBytes(long pos, byte[] bytes, int offset, int len)
getBinaryOutputStream(long pos) | setBinaryStream(long pos)
trim (long len) | truncate(long len)

Table 6-43 CLOB Method Equivalents

Oracle Proprietary Method (Deprecated) | JDBC 3.0 Standard Method Replacement
--- | ---
putString(long pos, String str) | setString(long pos, String str)
N/A | setString(long pos, String str, int offset, int len)
getAsciiOutputStream(long pos) | setAsciiStream(long pos)
getCharacterOutputStream(long pos) | setCharacterStream(long pos)
trim (long len) | truncate(long len)

### Reading Internal Persistent LOBs and External LOBs (BFILEs) with Java

With JDBC you can use Java to read both internal persistent LOBs and external LOBs (`BFILE`s).

#### BLOB, CLOB, and BFILE Classes

- BLOB and CLOB Classes. In JDBC theses classes provide methods for performing operations on large objects in the database including `BLOB` and `CLOB` data types.

- BFILE Class. In JDBC this class provides methods for performing operations on BFILE data in the database.

The `BLOB`, `CLOB`, and `BFILE` classes encapsulate LOB locators, so you do not deal with locators but instead use methods and properties provided to perform operations and get state information.

### Calling DBMS_LOB Package from Java (JDBC)

Any LOB functionality not provided by these classes can be accessed by a call to the PL/SQL `DBMS_LOB` package. This technique is used repeatedly in the examples throughout this manual.

### Referencing LOBs Using Java (JDBC)

You can get a reference to any of the preceding LOBs in the following two ways:

- As a column of an `OracleResultSet`

- As an `OUT` type PL/SQL parameter from an `OraclePreparedStatement`

#### Using OracleResultSet: BLOB and CLOB Objects Retrieved

When `BLOB` and `CLOB` objects are retrieved as a part of an `OracleResultSet`, these objects represent LOB locators of the currently selected row.

If the current row changes due to a move operation, for example, `rset`.`next`(), then the retrieved locator still refers to the original LOB row.

To retrieve the locator for the most current row, you must call `getBLOB()`, `getCLOB()`, or `getBFILE()` on the `OracleResultSet` each time a move operation is made depending on whether the instance is a `BLOB`, `CLOB` or `BFILE`.

### JDBC Syntax References and Further Information

For further JDBC syntax and information about using JDBC with LOBs:

See Also: Oracle Database JDBC Developer's Guide and Reference, "Working with LOBs and BFILEs" chapter, for detailed documentation, including parameters, parameter types, return values, and example code. `http://www.oracle.com/technology/index.html`

### JDBC Methods for Operating on LOBs

The following JDBC methods operate on `BLOB`s, `CLOB`s, and `BFILE`s:

- `BLOB`s:

  - To modify `BLOB` values, see Table 6-44

  - To read or examine `BLOB` values, see Table 6-45

  - For `BLOB` buffering, see Table 6-46

  - Temporary `BLOB`s: Creating, checking if LOB is open, and freeing. See Table 6-54

  - Opening, closing, and checking if `BLOB` is open, see Table 6-54

  - Truncating `BLOB`s, see Table 6-57

  - `BLOB` streaming API, see Table 6-59

- `CLOB`s:

  - To read or examine `CLOB` values, see Table 6-48

  - For `CLOB` buffering, see Table 6-49

  - To modify `CLOB`s, see Table 6-59

- Temporary `CLOB`s:

  - Opening, closing, and checking if `CLOB` is open, see Table 6-55

  - Truncating `CLOB`s, see Table 6-58

  - `CLOB` streaming API, see Table 6-60

- `BFILE`s:

  - To read or examine `BFILE`s, see Table 6-50

  - For `BFILE` buffering, see Table 6-51

  - Opening, closing, and checking if `BFILE` is open, see Table 6-56

  - BFILE streaming API, see Table 6-61

### JDBC oracle.sql.BLOB Methods to Modify BLOB Values

Table 6-44 JDBC oracle.sql.BLOB Methods To Modify BLOB Values

Method | Description
--- | ---
`int setBytes(long, byte[])` | Inserts the byte array into the `BLOB`, starting at the given offset

### JDBC oracle.sql.BLOB Methods to Read or Examine BLOB Values

Table 6-45 JDBC oracle.sql.BLOB Methods to Read or Examine BLOB Values

Method | Description
--- | ---
`byte[] getBytes(long, int)` | Gets the contents of the LOB as an array of bytes, given an offset
`long position(byte[],long)` | Finds the given byte array within the LOB, given an offset
`long position(Blob,long)` | Finds the given `BLOB` within the LOB
`public boolean equals(java.lang.Object)` | Compares this LOB with another. Compares the LOB locators.
`public long length()` | Returns the length of the LOB
`public int getChunkSize()` | Returns the `ChunkSize` of the LOB

### JDBC oracle.sql.BLOB Methods and Properties for BLOB Buffering

Table 6-46 JDBC oracle.sql.BLOB Methods and Properties for BLOB Buffering

Method | Description
--- | ---
`public java.io.InputStream getBinaryStream())` | Streams the LOB as a binary stream
`public java.io.OutputStream setBinaryStream()` | Retrieves a stream that can be used to write to the `BLOB` value that this `Blob` object represents

### JDBC oracle.sql.CLOB Methods to Modify CLOB Values

Table 6-47 JDBC oracle.sql.CLOB Methods to Modify CLOB Values

Method | Description
--- | ---
`int setString(long, java.lang.String)` | JDBC 3.0: Writes the given Java String to the CLOB value that this `Clob` object designates at the position `pos`.
`int putChars(long, char[])` | Inserts the character array into the LOB, starting at the given offset

### JDBC oracle.sql.CLOB Methods to Read or Examine CLOB Value

Table 6-48 JDBC oracle.sql.CLOB Methods to Read or Examine CLOB Values

Method | Description
--- | ---
`java.lang.String getSubString(long, int)` | Returns a substring of the LOB as a string
`int getChars(long, int, char[])` | Reads a subset of the LOB into a character array
`long position(java.lang.String, long)` | Finds the given String within the LOB, given an offset
`long position(oracle.jdbc2.Clob, long)` | Finds the given `CLOB` within the LOB, given an offset
`long length()` | Returns the length of the LOB
`int getChunkSize()` | R`eturns the ChunkSize` of the LOB

### JDBC oracle.sql.CLOB Methods and Properties for CLOB Buffering

Table 6-49 JDBC oracle.sql.CLOB Methods and Properties for CLOB Buffering

Method | Description
--- | ---
`java.io.InputStream getAsciiStream()` | Implements the `Clob` interface method. Gets the `CLOB` value designated by this `Clob` object as a stream of ASCII bytes
`java.io.OutputStream setAsciiStream(long pos)` | JDBC 3.0: Retrieves a stream to be used to write ASCII characters to the `CLOB` value that this `Clob` object represents, starting at position `pos`
`java.io.Reader getCharacterStream()` | Reads the `CLOB` as a character stream
`java.io.Writer setCharacterStream(long pos)` | JDBC 3.0: Retrieves a stream to be used to write Unicode characters to the `CLOB` value that this `Clob` object represents, starting at position `pos`

### JDBC oracle.sql.BFILE Methods to Read or Examine External LOB (BFILE) Values

Table 6-50 JDBC oracle.sql.BFILE Methods to Read or Examine External LOB (BFILE) Values

Method | Description
--- | ---
`byte[] getBytes(long, int)` | `G`ets the contents of the `BFILE` as an array of bytes, given an offset
`int getBytes(long, int, byte[])` | Reads a subset of the `BFILE` into a byte array
`long position(oracle.sql.BFILE, long)` | Finds the first appearance of the given `BFILE` contents within the LOB, from the given offset
`long position(byte[], long)` | Finds the first appearance of the given byte array within the `BFILE`, from the given offset
`long length()` | R`eturns the length of the BFILE`
`boolean fileExists()` | Checks if the operating system file referenced by this `BFILE` exists
`public void openFile()` | Opens the operating system file referenced by this `BFILE`
`public void closeFile()` | C`loses the operating system file referenced by this BFILE`
`public boolean isFileOpen()` | Checks if this `BFILE` is already open
`public java.lang.String getDirAlias()` | G`ets the directory object name for this BFILE`
`public java.lang.String getName()` | G`ets the file name referenced by this BFILE`

### JDBC oracle.sql.BFILE Methods and Properties for BFILE Buffering

Table 6-51 JDBC oracle.sql.BFILE Methods and Properties for BFILE Buffering

Method | Description
--- | ---
`public java.io.InputStream getBinaryStream()` | Reads `the BFILE` as a binary stream

### JDBC Temporary LOB APIs

Oracle Database JDBC drivers contain APIs to create and close temporary LOBs. These APIs can replace workarounds that use the following procedures from the `DBMS_LOB` PL/SQL package in prior releases:

- `DBMS_LOB.createTemporary()`

- `DBMS_LOB.isTemporary()`

- `DBMS_LOB.freeTemporary()`

Table 6-52 JDBC: Temporary BLOB APIs

Methods | Description
--- | ---
`public static BLOB createTemporary(Connection conn,` `boolean cache, int duration) throws SQLException` | Creates a temporary `BLOB`
`public static boolean isTemporary(BLOB blob)` `throws SQLException` | Checks if the specified `BLOB` locator refers to a temporary `BLOB`
`public boolean isTemporary() throws SQLException` | Checks if the current `BLOB` locator refers to a temporary `BLOB`
`public static void freeTemporary(BLOB temp_blob)` `throws SQLException` | Frees the specified temporary `BLOB`
`public void freeTemporary() throws SQLException` | Frees the temporary `BLOB`

Table 6-53 JDBC: Temporary CLOB APIs

Methods | Description
--- | ---
`public static CLOB createTemporary(Connection conn,` `boolean cache, int duration) throws SQLException` | Creates a temporary `CLOB`
`public static boolean isTemporary(CLOB clob)` `throws SQLException` | Checks if the specified `CLOB` locator refers to a temporary `CLOB`
`public boolean isTemporary() throws SQLException` | Checks if the current `CLOB` locator refers to a temporary `CLOB`
`public static void freeTemporary(CLOB temp_clob)` `throws SQLException` | Frees the specified temporary `CLOB`
`public void freeTemporary() throws SQLException` | Frees the temporary `CLOB`

### JDBC: Opening and Closing LOBs

`oracle.sql.CLOB` class is the Oracle JDBC driver implementation of standard JDBC `java.sql.Clob` interface. Table 6-53 lists the Oracle extension APIs in `oracle.sql.CLOB` for accessing temporary CLOBs.

Oracle Database JDBC drivers contain APIs to explicitly open and close LOBs. These APIs replace previous techniques that use `DBMS_LOB.open()` and `DBMS_LOB.close()`.

### JDBC: Opening and Closing BLOBs

`oracle.sql.BLOB` class is the Oracle JDBC driver implementation of standard JDBC java.sql.Blob interface. Table 6-54 lists the Oracle extension APIs in `oracle.sql.BLOB` that open and close BLOBs.

Table 6-54 JDBC: Opening and Closing BLOBs

Methods | Description
--- | ---
`public void open(int mode) throws SQLException` | Opens the `BLOB`
`public boolean isOpen() throws SQLException` | Sees if the `BLOB` is open
`public void close() throws SQLException` | Closes the `BLOB`

#### Opening the BLOB Using JDBC

To open a `BLOB`, your JDBC application can use the open method as defined in `oracle.sql.BLOB` class as follows:

    /**
     * Open a BLOB in the indicated mode. Valid modes include MODE_READONLY,
     * and MODE_READWRITE. It is an error to open the same LOB twice.
     */
    public void open (int mode) throws SQLException

Possible values of the mode parameter are:

    public static final int MODE_READONLY
    public static final int MODE_READWRITE

Each call to open opens the BLOB. For example:

    BLOB blob = ...
    blob.open (BLOB.MODE_READWRITE);

#### Checking If the BLOB Is Open Using JDBC

To see if a `BLOB` is opened, your JDBC application can use the `isOpen` method defined in oracle.sql.BLOB. The return Boolean value indicates whether the `BLOB` has been previously opened or not. The `isOpen` method is defined as follows:

    /**
     * Check whether the BLOB is opened.
     * @return true if the LOB is opened.
     */
     public boolean isOpen () throws SQLException

The usage example is:

    BLOB blob = ...
    // See if the BLOB is opened
    boolean isOpen = blob.isOpen ();

#### Closing the BLOB Using JDBC

To close a BLOB, your JDBC application can use the close method defined in `oracle.sql.BLOB`. The close API is defined as follows:

    /**
      * Close a previously opened BLOB.
      */
    public void close () throws SQLException

The usage example is:

    BLOB blob = ...
    // close the BLOB
    blob.close ();

### JDBC: Opening and Closing CLOBs

Class `oracle.sql.CLOB` is the Oracle JDBC driver implementation of the standard JDBC `java.sql.Clob` interface. Table 6-55 lists the Oracle extension APIs in `oracle.sql.CLOB` to open and close CLOBs.

Table 6-55 JDBC: Opening and Closing CLOBs

Methods | Description
--- | ---
`public void open(int mode) throws SQLException` | Open the `CLOB`
`public boolean isOpen() throws SQLExceptio` | See if the `CLOB` is opened
`public void close() throws SQLException` | Close the `CLOB`

#### Opening the CLOB Using JDBC

To open a `CLOB`, your JDBC application can use the open method defined in `oracle.sql.CLOB` class as follows:

    /**
     * Open a CLOB in the indicated mode. Valid modes include MODE_READONLY,
     * and MODE_READWRITE. It is an error to open the same LOB twice.
     */
    public void open (int mode) throws SQLException

The possible values of the mode parameter are:

    public static final int MODE_READONLY
    public static final int MODE_READWRITE

Each call to open opens the `CLOB`. For example,

    CLOB clob = ...
    clob.open (CLOB.MODE_READWRITE);

#### Checking If the CLOB Is Open Using JDBC

To see if a `CLOB` is opened, your JDBC application can use the `isOpen` method defined in oracle.sql.CLOB. The return Boolean value indicates whether the `CLOB` has been previously opened or not. The `isOpen` method is defined as follows:

    /**
      * Check whether the CLOB is opened.
      * @return true if the LOB is opened.
      */
    public boolean isOpen () throws SQLException

The usage example is:

    CLOB clob = ...
     // See if the CLOB is opened
     boolean isOpen = clob.isOpen ();

#### Closing the CLOB Using JDBC

To close a `CLOB`, the JDBC application can use the close method defined in `oracle.sql.CLOB`. The close API is defined as follows:

    /**
    * Close a previously opened CLOB.
    */
    public void close () throws SQLException

The usage example is:

    CLOB clob = ...
    // close the CLOB
    clob.close ();

### JDBC: Opening and Closing BFILEs

`oracle.sql.BFILE` class wraps the database `BFILE` object. Table 6-56 lists the Oracle extension APIs in `oracle.sql.BFILE` for opening and closing `BFILE`s.

Table 6-56 JDBC API Extensions for Opening and Closing BFILEs

Methods | Description
--- | ---
`public void open() throws SQLException` | Opens the `BFILE`
`public void open(int mode) throws SQLException` | Opens the `BFILE`
`public boolean isOpen() throws SQLException` | Checks if the `BFILE` is open
`public void close() throws SQLException` | Closes the `BFILE`

#### Opening BFILEs

To open a `BFILE`, your JDBC application can use the `OPEN` method defined in `oracle.sql.BFILE` class as follows:

    /**
     * Open a external LOB in the readonly mode. It is an error
     * to open the same LOB twice.
     */
    public void open () throws SQLException

    /**
     * Open a external LOB in the indicated mode. Valid modes include
     * MODE_READONLY only. It is an error to open the same
     * LOB twice.
     */
    public void open (int mode) throws SQLException

The only possible value of the mode parameter is:

    public static final int MODE_READONLY

Each call to open opens the `BFILE`. For example,

    BFILE bfile = ...
    bfile.open ();

#### Checking If the BFILE Is Open

To see if a `BFILE` is opened, your JDBC application can use the `isOpen` method defined in `oracle.sql.BFILE`. The return Boolean value indicates whether the BFILE has been previously opened or not. The `isOpen` method is defined as follows:

    /**
     * Check whether the BFILE is opened.
     * @return true if the LOB is opened.
     */
    public boolean isOpen () throws SQLException

The usage example is:

    BFILE bfile = ...
    // See if the BFILE is opened
    boolean isOpen = bfile.isOpen ();

#### Closing the BFILE

To close a `BFILE`, your JDBC application can use the `close` method defined in `oracle.sql.BFILE`. The `close` API is defined as follows:

    /**
     * Close a previously opened BFILE.
    */
    public void close () throws SQLException

The usage example is --

    BFILE bfile = ...
    // close the BFILE
    bfile.close ();

#### Usage Example (OpenCloseLob.java)

    /*
     * This sample shows how to open/close BLOB and CLOB.
     */

    // You need to import the java.sql package to use JDBC
    import java.sql.*;

    // You need to import the oracle.sql package to use oracle.sql.BLOB
    import oracle.sql.*;

    class OpenCloseLob
    {
      public static void main (String args [])
           throws SQLException
      {
        // Load the Oracle JDBC driver
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

        String url = "jdbc:oracle:oci8:@";
        try {
          String url1 = System.getProperty("JDBC_URL");
          if (url1 != null)
            url = url1;
        } catch (Exception e) {
          // If there is any security exception, ignore it
          // and use the default
        }

        // Connect to the database
        Connection conn =
          DriverManager.getConnection (url, "scott", "tiger");
        // It is faster when auto commit is off
        conn.setAutoCommit (false);

        // Create a Statement
        Statement stmt = conn.createStatement ();

        try
        {
          stmt.execute ("drop table basic_lob_table");
        }
        catch (SQLException e)
        {
          // An exception could be raised here if the table did not exist already.
        }

    // Create a table containing a BLOB and a CLOB
    stmt.execute ("create table basic_lob_table (x varchar2 (30), b blob, c clob)");

    // Populate the table
    stmt.execute (
        "insert into basic_lob_table values"
        + " ('one', '010101010101010101010101010101', 'onetwothreefour')");

        // Select the lobs
        ResultSet rset = stmt.executeQuery ("select * from basic_lob_table");
        while (rset.next ())
        {
          // Get the lobs
          BLOB blob = (BLOB) rset.getObject (2);
          CLOB clob = (CLOB) rset.getObject (3);

          // Open the lobs
          System.out.println ("Open the lobs");
          blob.open (BLOB.MODE_READWRITE);
          clob.open (CLOB.MODE_READWRITE);

          // Check if the lobs are opened
          System.out.println ("blob.isOpen()="+blob.isOpen());
          System.out.println ("clob.isOpen()="+clob.isOpen());

          // Close the lobs
          System.out.println ("Close the lobs");
          blob.close ();
          clob.close ();

          // Check if the lobs are opened
          System.out.println ("blob.isOpen()="+blob.isOpen());
          System.out.println ("clob.isOpen()="+clob.isOpen());
        }

        // Close the ResultSet
        rset.close ();

        // Close the Statement
        stmt.close ();

        // Close the connection
        conn.close ();
      }
    }

### Truncating LOBs Using JDBC

Oracle Database JDBC drivers contain APIs to truncate persistent LOBs. These APIs replace previous techniques that used `DBMS_LOB.trim()`.

#### JDBC: Truncating BLOBs

`oracle.sql.BLOB` class is Oracle JDBC driver implementation of the standard JDBC `java.sql.Blob` interface. Table 6-57 lists the Oracle extension API in `oracle.sql.BLOB` that truncates BLOBs.

Table 6-57 JDBC: Truncating BLOBs

Methods | Description
--- | ---
`public void truncate(long newlen) throws SQLException` | Truncates the `BLOB`

The truncate API is defined as follows:

    /**
    *Truncate the value of the BLOB to the length you specify in the newlen parameter.
     * @param newlen the new length of the BLOB.
     */
    public void truncate (long newlen) throws SQLException

The `newlen` parameter specifies the new length of the `BLOB`.

#### JDBC: Truncating CLOBs

`oracle.sql.CLOB` class is the Oracle JDBC driver implementation of standard JDBC java.sql.Clob interface. Table 6-58 lists the Oracle extension API in `oracle.sql.CLOB` that truncates `CLOB`s.

Table 6-58 JDBC: Truncating CLOBs

Methods | Description
--- | ---
`public void truncate(long newlen) throws SQLException` | Truncates the `CLOB`

The truncate API is defined as follows:

    /**
    *Truncate the value of the CLOB to the length you specify in the newlen parameter.
     * @param newlen the new length of the CLOB.
     */
    public void truncate (long newlen) throws SQLException

The `newlen` parameter specifies the new length of the `CLOB`.

See: "Trimming LOB Data", for an example.

### JDBC BLOB Streaming APIs

The JDBC interface provided with the database includes LOB streaming APIs that enable you to read from or write to a LOB at the requested position from a Java stream.

The `oracle.sql.BLOB` class implements the standard JDBC `java.sql.Blob` interface. Table 6-59 lists BLOB Streaming APIs.

Table 6-59 JDBC: BLOB Streaming APIs

Methods | Description
--- | ---
`public java.io.OutputStream` `setBinaryStream (long pos)` `throws SQLException` | JDBC 3.0: Retrieves a stream that can be used to write to the `BLOB` value that this `Blob` object represents, starting at position `pos`
`public java.io.InputStream` `getBinaryStream()` `throws SQLException` | JDBC 3.0: Retrieves a stream that can be used to read the `BLOB` value that this `Blob` object represents, starting at the beginning
`public java.io.InputStream` `getBinaryStream(long pos)` `throws SQLException` | Oracle extension: Retrieves a stream that can be used to read the `BLOB` value that this `Blob` object represents, starting at position `pos`

These APIs are defined as follows:

    /**
     * Write to the BLOB from a stream at the requested position.
     *
     * @param pos is the position data to be put.
     * @return a output stream to write data to the BLOB
     */
    public java.io.OutputStream setBinaryStream(long pos) throws SQLException

    /**
     * Read from the BLOB as a stream at the requested position.
     *
     * @param pos is the position data to be read.
     * @return a output stream to write data to the BLOB
     */
    public java.io.InputStream getBinaryStream(long pos) throws SQLException

### JDBC CLOB Streaming APIs

The `oracle.sql.CLOB` class is the Oracle JDBC driver implementation of standard JDBC `java.sql.Clob` interface. Table 6-60 lists the CLOB streaming APIs.

Table 6-60 JDBC: CLOB Streaming APIs

Methods | Description
--- | ---
`public java.io.OutputStream` `setAsciiStream (long pos) throws SQLException` | JDBC 3.0: Retrieves a stream to be used to write ASCII characters to the `CLOB` value that this `Clob` object represents, starting at position `pos`
`public java.io.Writer` `setCharacterStream (long pos) throws SQLException` | JDBC 3.0: Retrieves a stream to be used to write Unicode characters to the `CLOB` value that this `Clob` object represents, starting, at position `pos`
`public java.io.InputStream` `getAsciiStream() throws SQLException` | JDBC 3.0: Retrieves a stream that can be used to read ASCII characters from the `CLOB` value that this `Clob` object represents, starting at the beginning
`public java.io.InputStream` `getAsciiStream(long pos)` `throws SQLException` | Oracle extension: Retrieves a stream that can be used to read ASCII characters from the `CLOB` value that this `Clob` object represents, starting at position `pos`
`public java.io.Reader` `getCharacterStream() throws SQLException` | JDBC 3.0: Retrieves a stream that can be used to read Unicode characters from the `CLOB` value that this `Clob` object represents, starting at the beginning
`public java.io.Reader` `getCharacterStream(long pos) throws SQLException` | Oracle extension: Retrieves a stream that can be used to read Unicode characters from the `CLOB` value that this `Clob` object represents, starting at position `pos`

These APIs are defined as follows:

    /**
      * Write to the CLOB from a stream at the requested position.
      * @param pos is the position data to be put.
      * @return a output stream to write data to the CLOB
      */
    public java.io.OutputStream setAsciiStream(long pos) throws SQLException

    /**

    * Write to the CLOB from a stream at the requested position.
         * @param pos is the position data to be put.
         * @return a output stream to write data to the CLOB
         */
      public java.io.Writer setCharacterStream(long pos) throws SQLException

        /**
         * Read from the CLOB as a stream at the requested position.
         * @param pos is the position data to be put.
         * @return a output stream to write data to the CLOB
         */
      public java.io.InputStream getAsciiStream(long pos) throws SQLException

       /**
        * Read from the CLOB as a stream at the requested position.
        * @param pos is the position data to be put.
        * @return a output stream to write data to the CLOB
        */
       public java.io.Reader getCharacterStream(long pos) throws SQLException

### BFILE Streaming APIs

`oracle.sql.BFILE` class wraps the database BFILEs. Table 6-61 lists the Oracle extension APIs in `oracle.sql.BFILE` that reads `BFILE` content from the requested position.

Table 6-61 JDBC: BFILE Streaming APIs

Methods | Description
--- | ---
`public java.io.InputStream` `getBinaryStream(long pos) throws SQLException` | Reads from the `BFILE` as a stream

These APIs are defined as follows:

    /**
     * Read from the BLOB as a stream at the requested position.
     *
     * @param pos is the position data to be read.
     * @return a output stream to write data to the BLOB
     */
    public java.io.InputStream getBinaryStream(long pos) throws SQLException

#### JDBC BFILE Streaming Example (NewStreamLob.java)

    /*
     * This sample shows how to read/write BLOB and CLOB as streams.
     */

    import java.io.*;

    // You need to import the java.sql package to use JDBC
    import java.sql.*;

    // You need to import the oracle.sql package to use oracle.sql.BLOB
    import oracle.sql.*;

    class NewStreamLob
    {
      public static void main (String args [])  throws Exception
      {
        // Load the Oracle JDBC driver
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

        String url = "jdbc:oracle:oci8:@";
        try {
          String url1 = System.getProperty("JDBC_URL");
          if (url1 != null)
            url = url1;
        } catch (Exception e) {
          // If there is any security exception, ignore it
          // and use the default
        }

        // Connect to the database
        Connection conn =
          DriverManager.getConnection (url, "scott", "tiger");
        // It is faster when auto commit is off
        conn.setAutoCommit (false);

        // Create a Statement
        Statement stmt = conn.createStatement ();

        try
        {
          stmt.execute ("drop table basic_lob_table");
        }
        catch (SQLException e)
        {
          // An exception could be raised here if the table did not exist already.
        }

        // Create a table containing a BLOB and a CLOB
        stmt.execute (
             "create table basic_lob_table"
             + "(x varchar2 (30), b blob, c clob)");

        // Populate the table
        stmt.execute (
             "insert into basic_lob_table values"
             + "('one', '010101010101010101010101010101', 'onetwothreefour')");

        System.out.println ("Dumping lobs");

        // Select the lobs
        ResultSet rset = stmt.executeQuery ("select * from basic_lob_table");
        while (rset.next ())
        {
          // Get the lobs
          BLOB blob = (BLOB) rset.getObject (2);
          CLOB clob = (CLOB) rset.getObject (3);

          // Print the lob contents
          dumpBlob (conn, blob, 1);
          dumpClob (conn, clob, 1);

          // Change the lob contents
          fillClob (conn, clob, 11, 50);
          fillBlob (conn, blob, 11, 50);
        }
        rset.close ();

        System.out.println ("Dumping lobs again");

        rset = stmt.executeQuery ("select * from basic_lob_table");
        while (rset.next ())
        {
          // Get the lobs
          BLOB blob = (BLOB) rset.getObject (2);
          CLOB clob = (CLOB) rset.getObject (3);

          // Print the lobs contents
          dumpBlob (conn, blob, 11);
          dumpClob (conn, clob, 11);
        }
        // Close all resources
        rset.close();
        stmt.close();
        conn.close();
      }

      // Utility function to dump Clob contents
      static void dumpClob (Connection conn, CLOB clob, long offset)
        throws Exception
      {
        // get character stream to retrieve clob data
        Reader instream = clob.getCharacterStream(offset);

        // create temporary buffer for read
        char[] buffer = new char[10];

        // length of characters read
        int length = 0;

        // fetch data
        while ((length = instream.read(buffer)) != -1)
        {
          System.out.print("Read " + length + " chars: ");

          for (int i=0; i<length; i++)
            System.out.print(buffer[i]);
          System.out.println();
        }

        // Close input stream
        instream.close();
      }

      // Utility function to dump Blob contents
      static void dumpBlob (Connection conn, BLOB blob, long offset)
        throws Exception
      {
        // Get binary output stream to retrieve blob data
        InputStream instream = blob.getBinaryStream(offset);
        // Create temporary buffer for read
        byte[] buffer = new byte[10];
        // length of bytes read
        int length = 0;
        // Fetch data
        while ((length = instream.read(buffer)) != -1)
        {
          System.out.print("Read " + length + " bytes: ");

          for (int i=0; i<length; i++)
            System.out.print(buffer[i]+" ");
          System.out.println();
        }

        // Close input stream
        instream.close();
      }

      // Utility function to put data in a Clob
      static void fillClob (Connection conn, CLOB clob, long offset, long length)
        throws Exception
      {
        Writer outstream = clob.setCharacterStream(offset);

        int i = 0;
        int chunk = 10;

        while (i < length)
        {
          outstream.write("aaaaaaaaaa", 0, chunk);

          i += chunk;
          if (length - i < chunk)
             chunk = (int) length - i;
        }
        outstream.close();
      }

      // Utility function to put data in a Blob
      static void fillBlob (Connection conn, BLOB blob, long offset, long length)
        throws Exception
      {
        OutputStream outstream = blob.setBinaryStream(offset);

        int i = 0;
        int chunk = 10;

        byte [] data = { 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 };

        while (i < length)
        {
          outstream.write(data, 0, chunk);

          i += chunk;
          if (length - i < chunk)
             chunk = (int) length - i;
        }
        outstream.close();
      }
    }

### JDBC and Empty LOBs

An empty `BLOB` can be created from the following API from `oracle.sql.BLOB`:

    public static BLOB empty_lob () throws SQLException

Similarly, the following API from `oracle.sql.CLOB` creates an empty `CLOB`:

    public static CLOB empty_lob () throws SQLException

Empty LOB instances are created by JDBC drivers without making database round trips. Empty LOBs can be used in the following cases:

- "set" APIs of PreparedStatement

- "update" APIs of updatable result set

- attribute value of STRUCTs

- element value of ARRAYs

  Note: Empty LOBs are special marker LOBs but not real LOB values.

JDBC applications cannot read or write to empty LOBs created from the preceding APIs. An ORA-17098 "Invalid empty lob operation" results if your application attempts to read/write to an empty LOB.

## Oracle Provider for OLE DB (OraOLEDB)

Oracle Provider for OLE DB (OraOLEDB) offers high performance and efficient access to Oracle data for OLE DB and ADO developers. Developers programming with Visual Basic, C++, or any COM client can use OraOLEDB to access Oracle databases.

OraOLEDB is an OLE DB provider for Oracle. It offers high performance and efficient access to Oracle data including LOBs, and also allows updates to certain LOB types.

The following LOB types are supported by OraOLEDB:

- For Persistent LOBs. READ/WRITE through the rowset.

- For BFILEs. READ-ONLY through the rowset.

- Temporary LOBs are not supported through the rowset.

  See Also: Oracle Provider for OLE DB Developer's Guide

## Overview of Oracle Data Provider for .NET (ODP.NET)

Oracle Data Provider for .NET (ODP.NET) is an implementation of a data provider for the Oracle database. ODP.NET uses Oracle native APIs to offer fast and reliable access to Oracle data and features from any .NET application. ODP.NET also uses and inherits classes and interfaces available in the Microsoft .NET Framework Class Library. The ODP.NET supports the following LOBs as native datatypes with .NET: `BLOB`, `CLOB`, `NCLOB`, and `BFILE`.

See Also: Oracle Data Provider for .NET Developer's Guide